Load data from files:
library(tidyverse)
library(cowplot)
library(readxl)
library(openxlsx)
library(plotly)
library(rcartocolor)
### Load Kynnetec AgroTrak pesticide data (proprietary):
AgroTrak.CornSoy.dat <- read_excel("AgroTrak_CornSoy_v1.xlsx",
skip = 5) %>%
select(Year, Crop,
Type = "Pesticide Type",
Active.Ingredient = "Active Ingredient",
Volume.lbs = "AI volume\r\n(lb)",
Base.Area.Treated = "Base Area Treated\r\n(acres)") %>%
mutate(ai = str_replace_all(Active.Ingredient, " \\(.\\)", ""))
glimpse(AgroTrak.CornSoy.dat)
### Load concordance file:
srcTab <- read_csv("cclistInEcotox.csv")
glimpse(srcTab)
### Load honey bee toxicity values from ecotox export:
ecotox.dat0 <-
read_excel("ECOTOX-Terrestrial-Export_20211207_plusManualAdd.xlsx",
sheet = "Terrestrial-Export") %>%
rename(cas.number = "CAS Number", chemName = "Chemical Name")
### Pare down ecotox values to those likely to be used in this analysis
### (leaving the full ecotox.dat0 just in case needed later),
### and join with the concordance list containing AgroTrak names:
ecotox.dat <- ecotox.dat0 %>%
select(source,
cas.number,
ConcType = `Conc 1 Type (Author)`,
Effect,
LifeStage = `Organism Lifestage`,
ExposureType = `Exposure Type`,
Endpoint,
ObservedDuration.d = `Observed Duration (Days)`,
ObsRespMean = `Observed Response Mean`,
ToxUnit = `Observed Response Units`) %>%
filter(Endpoint %in% c("LD50", "NOEL")) %>%
right_join(srcTab) %>%
select(-inEcoTox, -inManualAdd, -CompTox.PREFERRED_NAME)
glimpse(ecotox.dat)
Create a ranked list of pesticides by volume applied in AgroTrak:
AgroTrak.Ranked <- AgroTrak.CornSoy.dat %>%
group_by(Type, ai) %>%
summarize(Volume.lbs = round(sum(Volume.lbs, na.rm = TRUE)),
Base.Area.Treated.sum = round(sum(Base.Area.Treated,
na.rm = TRUE)),
nYears = length(unique(Year)),
Base.Area.perYear = round(Base.Area.Treated.sum / nYears),
Volume.perYear = round(Volume.lbs / nYears)) %>%
arrange(Type, -Volume.lbs)
AdultContactLD50 <- ecotox.dat %>%
filter(LifeStage == "Adult" &
ExposureType == "Dermal" &
Endpoint == "LD50") %>%
group_by(ai) %>%
mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
group_by(ai, hasAI) %>%
mutate(min.d.grp = min(abs(2 - ObservedDuration.d) + 2,
na.rm = TRUE)) %>%
filter(case_when(checkAI == TRUE ~
hasAI == TRUE & ObservedDuration.d <= min.d.grp + 2,
checkAI == FALSE ~
ObservedDuration.d <= min.d.grp + 2)) %>%
ungroup() %>%
select(-checkAI, -hasAI, -min.d.grp) %>%
group_by(ai) %>%
slice(which.min(ObsRespMean))
glimpse(AdultContactLD50)
unique(AdultContactLD50$ConcType)
unique(AdultContactLD50$ToxUnit)
AgroTrak.AdultContactLD50.Rank <- left_join(AgroTrak.Ranked, AdultContactLD50)
#write.xlsx(AgroTrak.AdultContactLD50.Rank,
# file = "Draft_AdultContactLD50.xlsx", asTable = TRUE)
AdultContactLD50.summary <- AgroTrak.AdultContactLD50.Rank %>%
group_by(Type) %>%
mutate(totalVol = sum(Volume.lbs),
pctVol = Volume.lbs / totalVol * 100,
toxVal = !is.na(ObsRespMean)) %>%
#select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
group_by(Type, toxVal) %>%
summarize(toxPct = sum(pctVol)) %>%
pivot_wider(names_from = toxVal,
values_from = toxPct) %>%
rename("missing Tox value" = `FALSE`,
"Tox value present" = `TRUE`)
knitr::kable(AdultContactLD50.summary, digits = 1)
| Type | missing Tox value | Tox value present |
|---|---|---|
| Fungicide | 6.0 | 94.0 |
| Herbicide | 3.4 | 96.6 |
| Insecticide | 8.3 | 91.7 |
List of active ingredients that made up greater than 1% of a group’s volume in any single year but has no adult dermal LD50 value:
CornSoyAdultContact.dat %>%
filter(is.na(RQ.pctOfType) & Volume.pctOfType > 1) %>%
distinct(Type, ai) %>%
arrange(Type)
## # A tibble: 17 x 2
## Type ai
## <chr> <chr>
## 1 Fungicide SULFUR
## 2 Fungicide TEBUCONAZOLE
## 3 Fungicide COPPER SULFATE
## 4 Fungicide TETRACONAZOLE
## 5 Fungicide FLUSILAZOLE
## 6 Fungicide PETROLEUM OIL
## 7 Fungicide PHOSPHORIC ACID
## 8 Herbicide CYANAZINE
## 9 Herbicide ACIFLUORFEN
## 10 Herbicide BENTAZONE
## 11 Herbicide FOMESAFEN
## 12 Herbicide SULFOSATE
## 13 Herbicide GLUFOSINATE
## 14 Herbicide DIMETHENAMID-P
## 15 Insecticide TEBUPIRIMPHOS
## 16 Insecticide BIFENTHRIN
## 17 Insecticide ZETA-CYPERMETHRIN
Total pesticide volume applied.
Pesticide volume applied per treated acre.
Summed honey bee risk quotient values for all pesticides.
Summed honey bee risk quotient values per treated acre.
Same RQ plots, faceted & scaled to see fungicide & herbicide trends.
## `summarise()` regrouping output by 'Crop', 'Type' (override with `.groups`
## argument)`summarise()` regrouping output by 'Year', 'Crop', 'Type' (override
## with `.groups` argument)
## Rows: 1,248
## Columns: 7
## Groups: Year, Crop, Type [116]
## $ Year <dbl> 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, …
## $ Crop <chr> "Corn", "Corn", "Corn", "Corn", "Corn", "Corn", "Corn"…
## $ Type <chr> "Herbicide", "Herbicide", "Herbicide", "Herbicide", "H…
## $ ai.top <chr> "2,4-D", "ACETOCHLOR", "ALACHLOR", "ATRAZINE", "DICAMB…
## $ BeeREX.RQ <dbl> 446199.10, 463729.70, 591170.16, 1927358.29, 165447.57…
## $ BeeREX.RQ.acre <dbl> 0.07176011, 0.02434560, 0.15482690, 0.03452120, 0.0101…
## $ RQ.pctOfType <dbl> 7.6780180, 7.9796776, 10.1726227, 33.1652207, 2.846956…
| Crop | Type | ai | VolumeContrib | RQContrib | Diff.11 |
|---|---|---|---|---|---|
| Corn | Insecticide | FIPRONIL | 1.0 | 21.1 | 20.1 |
| Corn | Insecticide | CHLORPYRIFOS | 24.5 | 38.8 | 14.3 |
| Corn | Insecticide | PERMETHRIN | 1.4 | 11.9 | 10.5 |
| Corn | Insecticide | CYHALOTHRIN-LAMBDA | 1.6 | 9.3 | 7.7 |
| Corn | Insecticide | DELTAMETHRIN | 0.0 | 6.2 | 6.2 |
| Corn | Insecticide | CYFLUTHRIN | 0.8 | 5.3 | 4.5 |
| Corn | Insecticide | CYHALOTHRIN-GAMMA | 0.0 | 1.5 | 1.5 |
| Corn | Insecticide | ESFENVALERATE | 0.1 | 1.2 | 1.1 |
| Corn | Insecticide | ALPHA-CYPERMETHRIN | 0.1 | 0.9 | 0.8 |
| Corn | Insecticide | CHLORETHOXYFOS | 0.7 | 1.5 | 0.8 |
| Corn | Insecticide | INDOXACARB | 0.2 | 0.9 | 0.7 |
| Corn | Insecticide | TEFLUTHRIN | 8.2 | 8.7 | 0.5 |
| Corn | Insecticide | DIMETHOATE | 1.7 | 2.1 | 0.4 |
| Corn | Insecticide | IMIDACLOPRID | 0.1 | 0.5 | 0.4 |
| Corn | Insecticide | ABAMECTIN | 0.0 | 0.3 | 0.3 |
| Corn | Insecticide | SPINETORAM | 0.0 | 0.2 | 0.2 |
| Corn | Insecticide | CYPERMETHRIN | 0.1 | 0.2 | 0.1 |
| Corn | Insecticide | ACETAMIPRID | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | AZADIRACHTIN | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | CYFLUMETOFEN | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | DISULFOTON | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | FLUPYRADIFURONE | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | MALATHION | 0.2 | 0.2 | 0.0 |
| Corn | Insecticide | METALDEHYDE | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | PIPERONYL BUTOXIDE | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | PYRETHRINS | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | ROTENONE | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | SPINOSYN | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | SULFOXAFLOR | 0.0 | 0.0 | 0.0 |
| Corn | Insecticide | CHLORANTRANILIPROLE | 0.1 | 0.0 | -0.1 |
| Corn | Insecticide | DIAZINON | 0.1 | 0.0 | -0.1 |
| Corn | Insecticide | FENPYROXIMATE | 0.1 | 0.0 | -0.1 |
| Corn | Insecticide | FLUBENDIAMIDE | 0.1 | 0.0 | -0.1 |
| Corn | Insecticide | METHOXYFENOZIDE | 0.1 | 0.0 | -0.1 |
| Corn | Insecticide | METHOMYL | 0.2 | 0.0 | -0.2 |
| Corn | Insecticide | CARBARYL | 0.4 | 0.1 | -0.3 |
| Corn | Insecticide | ETOXAZOLE | 0.4 | 0.0 | -0.4 |
| Corn | Insecticide | PHORATE | 0.5 | 0.0 | -0.5 |
| Corn | Insecticide | METHYL PARATHION | 1.3 | 0.7 | -0.6 |
| Corn | Insecticide | ETHOPROPHOS | 0.9 | 0.1 | -0.8 |
| Corn | Insecticide | HEXYTHIAZOX | 0.8 | 0.0 | -0.8 |
| Corn | Insecticide | ACEPHATE | 1.7 | 0.3 | -1.4 |
| Corn | Insecticide | SPIROMESIFEN | 1.4 | 0.0 | -1.4 |
| Corn | Insecticide | CARBOFURAN | 5.5 | 4.0 | -1.5 |
| Corn | Insecticide | SULFUR | 4.4 | 0.0 | -4.4 |
| Corn | Insecticide | TERBUFOS | 8.7 | 0.4 | -8.3 |
| Corn | Insecticide | PROPARGITE | 30.8 | 0.1 | -30.7 |
| Corn | Insecticide | BACILLUS THURINGIENSIS | 0.2 | NaN | NaN |
| Corn | Insecticide | BIFENTHRIN | 6.7 | NaN | NaN |
| Corn | Insecticide | HARPIN PROTEIN | 0.0 | NaN | NaN |
| Corn | Insecticide | TEBUPIRIMPHOS | 6.9 | NaN | NaN |
| Corn | Insecticide | ZETA-CYPERMETHRIN | 0.3 | NaN | NaN |
| Crop | Type | ai | VolumeContrib | RQContrib | Diff.11 |
|---|---|---|---|---|---|
| Soybeans | Insecticide | CYHALOTHRIN-LAMBDA | 6.2 | 17.8 | 11.6 |
| Soybeans | Insecticide | DELTAMETHRIN | 0.1 | 6.0 | 5.9 |
| Soybeans | Insecticide | ESFENVALERATE | 1.0 | 5.8 | 4.8 |
| Soybeans | Insecticide | CYFLUTHRIN | 1.5 | 5.6 | 4.1 |
| Soybeans | Insecticide | THIAMETHOXAM | 0.7 | 4.4 | 3.7 |
| Soybeans | Insecticide | CYHALOTHRIN-GAMMA | 0.2 | 3.5 | 3.3 |
| Soybeans | Insecticide | PERMETHRIN | 0.8 | 3.5 | 2.7 |
| Soybeans | Insecticide | SPINOSYN | 0.0 | 1.7 | 1.7 |
| Soybeans | Insecticide | ABAMECTIN | 0.0 | 1.1 | 1.1 |
| Soybeans | Insecticide | ALPHA-CYPERMETHRIN | 0.2 | 1.0 | 0.8 |
| Soybeans | Insecticide | IMIDACLOPRID | 1.6 | 2.3 | 0.7 |
| Soybeans | Insecticide | SPINETORAM | 0.2 | 0.8 | 0.6 |
| Soybeans | Insecticide | FIPRONIL | 0.0 | 0.6 | 0.6 |
| Soybeans | Insecticide | CLOTHIANIDIN | 0.1 | 0.6 | 0.5 |
| Soybeans | Insecticide | INDOXACARB | 0.2 | 0.3 | 0.1 |
| Soybeans | Insecticide | AFIDOPYROPEN | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | ETOXAZOLE | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | PIPERONYL BUTOXIDE | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | PYRETHRINS | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | ROTENONE | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | SULFOXAFLOR | 0.2 | 0.2 | 0.0 |
| Soybeans | Insecticide | TRALOMETHRIN | 0.0 | 0.0 | 0.0 |
| Soybeans | Insecticide | CYPERMETHRIN | 0.3 | 0.2 | -0.1 |
| Soybeans | Insecticide | ACETAMIPRID | 0.1 | 0.0 | -0.1 |
| Soybeans | Insecticide | DICROTOPHOS | 0.1 | 0.0 | -0.1 |
| Soybeans | Insecticide | FLUPYRADIFURONE | 0.1 | 0.0 | -0.1 |
| Soybeans | Insecticide | NOVALURON | 0.1 | 0.0 | -0.1 |
| Soybeans | Insecticide | CARBOFURAN | 0.4 | 0.2 | -0.2 |
| Soybeans | Insecticide | DIFLUBENZURON | 0.2 | 0.0 | -0.2 |
| Soybeans | Insecticide | METHOMYL | 0.2 | 0.0 | -0.2 |
| Soybeans | Insecticide | DIMETHOATE | 0.7 | 0.4 | -0.3 |
| Soybeans | Insecticide | METHYL PARATHION | 0.7 | 0.3 | -0.4 |
| Soybeans | Insecticide | MALATHION | 1.3 | 0.9 | -0.4 |
| Soybeans | Insecticide | THIODICARB | 0.5 | 0.0 | -0.5 |
| Soybeans | Insecticide | METHOXYFENOZIDE | 0.6 | 0.0 | -0.6 |
| Soybeans | Insecticide | BIFENAZATE | 0.9 | 0.0 | -0.9 |
| Soybeans | Insecticide | CARBARYL | 1.5 | 0.2 | -1.3 |
| Soybeans | Insecticide | FLUBENDIAMIDE | 1.6 | 0.0 | -1.6 |
| Soybeans | Insecticide | PHORATE | 1.7 | 0.0 | -1.7 |
| Soybeans | Insecticide | ALDICARB | 3.1 | 1.1 | -2.0 |
| Soybeans | Insecticide | CHLORANTRANILIPROLE | 2.2 | 0.0 | -2.2 |
| Soybeans | Insecticide | CHLORPYRIFOS | 47.7 | 45.2 | -2.5 |
| Soybeans | Insecticide | SULFUR | 3.0 | 0.0 | -3.0 |
| Soybeans | Insecticide | CHLOROPICRIN | 4.8 | 0.0 | -4.8 |
| Soybeans | Insecticide | DICHLOROPROPENE | 8.4 | 0.0 | -8.4 |
| Soybeans | Insecticide | ACEPHATE | 23.6 | 2.3 | -21.3 |
| Soybeans | Insecticide | BACILLUS THURINGIENSIS | 0.1 | NaN | NaN |
| Soybeans | Insecticide | BIFENTHRIN | 7.1 | NaN | NaN |
| Soybeans | Insecticide | HARPIN PROTEIN | 0.0 | NaN | NaN |
| Soybeans | Insecticide | POLYHEDROSIS VIRUS | 0.1 | NaN | NaN |
| Soybeans | Insecticide | ZETA-CYPERMETHRIN | 0.7 | NaN | NaN |